問題描述
SQL2005：將一個錶鍊接到多個表並保持參考完整性？ (SQL2005: Linking a table to multiple tables and retaining Ref Integrity?)
表：消息字段：ID、RelationID、RelationType、OtherFields...</pre>
但這阻止了我使用約束來強制我的參照完整性。我也可以預見到它會在稍後使用 Linq to SQL 與開發方面產生問題。
是否有一個優雅的解決方案來解決這個問題，或者我最終將不得不一起破解一些東西？
燒傷
參考解法
方法 1:
Create one Message table, containing a unique MessageId and the various properties you need to store for a message.
Table: Message
Fields: Id, TimeReceived, MessageDetails, WhateverElse...
Create two link tables ‑ QuoteMessage and JobMessage. These will just contain two fields each, foreign keys to the Quote/Job and the Message.
Table: QuoteMessage
Fields: QuoteId, MessageId
Table: JobMessage
Fields: JobId, MessageId
In this way you have defined the data properties of a Message in one place only (making it easy to extend, and to query across all messages), but you also have the referential integrity linking Quotes and Jobs to any number of messages. Indeed, both a Quote and Job could be linked to the same message (I'm not sure if that is appropriate to your business model, but at least the data model gives you the option).
方法 2:
About the only other way I can think of is to have a base Message table, with both an Id and a TypeId. Your subtables (QuoteMessage and JobMessage) then reference the base table on both MessageId and TypeId ‑ but also have CHECK CONSTRAINTS on them to enforce only the appropiate MessageTypeId.
Table: Message
Fields: Id, MessageTypeId, Text, ...
Primary Key: Id, MessageTypeId
Unique: Id
Table: MessageType
Fields: Id, Name
Values: 1, "Quote" : 2, "Job"
Table: QuoteMessage
Fields: Id, MessageId, MessageTypeId, QuoteId
Constraints: MessageTypeId = 1
References: (MessageId, MessageTypeId) = (Message.Id, Message.MessageTypeId)
QuoteId = Quote.QuoteId
Table: JobMessage
Fields: Id, MessageId, MessageTypeId, JobId
Constraints: MessageTypeId = 2
References: (MessageId, MessageTypeId) = (Message.Id, Message.MessageTypeId)
JobId = Job.QuoteId
What does this buy you, as compared to just a JobMesssage and QuoteMessage table? It elevates a Message to a first class citizen, so that you can read all Messages from a single table. In exchange, your query path from a Message to it's relevant Quote or Job is 1 more join away. It kind of depends on your app flow whether that's a good tradeoff or not.
As for 2 identical tables violating DRY ‑ I wouldn't get hung up on that. In DB design, it's less about DRY, and more about normalization. If the 2 things you're modeling have the same attributes (columns), but are actually different things (tables) ‑ then it's reasonable to have multiple tables with similar schemas. Much better than the reverse of munging different things together.
方法 3:
<p>@burns</p>Ian's answer (+1) is correct [see note]. Using a many to many table QUOTEMESSAGE to join QUOTE to MESSAGE is the most correct model, but will leave orphaned MESSAGE records.
This is one of those rare cases where a trigger can be used. However, caution needs to be applied to ensure that the a single MESSAGE record cannot be associated with both a QUOTE and a JOB.
create trigger quotemessage_trg
on quotemessage
for delete
as
begin
delete
from [message]
where [message].[msg_id] in
(select [msg_id] from Deleted);
end
Note to Ian, I think there is a typo in the table definition for JobMessage, where the columns should be JobId, MessageId (?). I would edit your quote but it might take me a few years to gain that level of reputation!
方法 4:
Why not just have both QuoteId and JobId fields in the message table? Or does a message have to be regarding either a quote or a job and not both?
(by littlecharva、Ian Nelson、Mark Brackett、Guy、Blorgbeard)